BigQueryのコミュニティ提供のユーザー関数を使ってみる

#Google Cloud (GCP)

#Google BigQuery

kobayashi.m

2022.09.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

BigQueryでは他のDWHサービスと同様にユーザー定義関数が使えます。BigQueryのユーザー定義関数はSQLだけでなくJavascriptでも定義できることが特徴です。そのユーザー定義関数でコミュニティ提供の関数として一般的に使われる関数がはじめから定義されています。今回そのコミュニティ提供の関数を使ってみたいと思います。

コミュニティ提供の関数とは

一般的によく使われるユーザー定義関数がコミュニティ提供の関数として一般公開データセットbigquery-public-dataのpersistent_udfsに作成されています。一般公開データセットなのでBigQueryの中で簡単に呼び出すことができます。またそのソースはGitHubのbigquery-utilsリポジトリ で確認することができます。このリポジトリはGitHubでパブリックになっているので必要であればプルリクを送って新たなコミュニティ提供の関数を追加することもできます。

現在登録されているコミュニティ提供の関数の一覧はGitHubのREADME.md か一般公開データセットのbigquery-public-data.persistent_udfsで確認できますが、GitHubのREADME.md では具体的な使い方も記載されているのでそちらで確認することをおすすめします。

bigquery-utils/udfs at master · GoogleCloudPlatform/bigquery-utils · GitHub

コミュニティ提供の関数を使ってみる

今回使うのはURL文字列かURLパラメータを取得する関数url_param（bigquery-utils/udfs/community at master · GoogleCloudPlatform/bigquery-utils · GitHub ）を使ってみます。 BigQueryの一般公開データセットの中のbigquery-public-data.breathe.springerテーブルのpdf_linkカラムがURL文字列ですのでこれを対象にします。

コミュニティ提供の関数はbqutil.fn.関数名()の形で使います。今回なurl_param(query STRING, p STRING)の形で使用すれば良いので以下のSQLを実行してみます。

SELECT 
  id
  ,date
  ,pdf_link
  ,bqutil.fn.url_param(pdf_link,'id')
FROM `bigquery-public-data.breathe.springer` LIMIT 1000

また他のDWHの独自機能を実現するコミュニティ提供の関数もあります。

bigquery-utils/udfs/migration at master · GoogleCloudPlatform/bigquery-utils · GitHub

この中のRedshiftのsplit_part(string STRING, delimiter STRING, part INT64)を使ってみます。先程と違いbqutil.rs.関数名()で使う必要があります。

SELECT 
  id
  ,date
  ,pdf_link
  ,bqutil.fn.url_param(pdf_link,'id')
  ,bqutil.rs.split_part(CAST(date as string), '-' , 1)
  ,bqutil.rs.split_part(CAST(date as string), '-' , 2)
  ,bqutil.rs.split_part(CAST(date as string), '-' , 3)
FROM `bigquery-public-data.breathe.springer` LIMIT 1000

springer.dateカラムはDate型のためstringにcastしてからsplit_part関数に引数として与えています。

こちらも簡単にUDF関数を使うことができました。

まとめ

GAされた「コミュニティ提供の関数」を使ってみました。標準関数では実装されていない処理もコミュニティ提供の関数にあるので色々使いみちはあるかと思います。一点注意点としてbigquery-public-data.persistent_udfsはロケーションがUSのためbqutil.fn.関数名()で扱えるのはデータロケーションがUSのデータセットのみとなります。それ以外のデータロケーションで使う場合にはCloud Buildなどを使ってデプロイする 必要があります。

最後まで読んで頂いてありがとうございました。